const db = require("../db");
const db_execute = async (sql, params) => {
    if (!params) {
        return db.execute(sql, params); // 没有参数直接执行
    } else {
        const new_params = params.map(item => {
            if (typeof item === 'number') {
                return item.toString();  // 如果是数字，转换成字符串
            }
            return item;
        });
        return db.execute(sql, new_params); // 执行查询并传递转换后的参数
    }
};

class Leave {

    static async addLeave(leaveData) {
        try {
            const { employee_id, leave_type, leave_start_date, leave_end_date, leave_days, leave_reason, leave_approved_by, leave_status } = leaveData
            const query = `INSERT INTO leave_management (employee_id, leave_type, leave_start_date, leave_end_date,leave_days, leave_reason, leave_approved_by, leave_status, leave_request_date) VALUES (?,?,?,?,?,?,?,?,NOW())`
            const result = await db_execute(query, [employee_id, leave_type, leave_start_date, leave_end_date, leave_days, leave_reason, leave_approved_by, leave_status])
            return result[0].affectedRows


        } catch (error) {
            throw new Error('添加请假记录失败: ' + error.message)

        }
    }

    static async getAllLeave(currentPage = 1, pageSize = 10, searchMsg) {
        try {
            const limit = (currentPage - 1) * pageSize

            let searchWord = []
            let queryParams = []

            if (searchMsg.employee_id) {
                searchWord.push(`employee_id = ?`)
                queryParams.push(Number(searchMsg.employee_id))
            }


            if (searchMsg.status) {
                searchWord.push(`leave_status = ?`)
                queryParams.push(searchMsg.status)
            }
            queryParams.push(pageSize, limit)
            const queryWhere = searchWord.length > 0 ? "WHERE " + searchWord.join(" AND ") : " "
            const query = `SELECT * FROM leave_management ${queryWhere} LIMIT ? OFFSET ?`
            const [rows] = await db_execute(query, queryParams)
            queryParams = queryParams.slice(0, queryParams.length - 2)
            const queryCount = `SELECT COUNT(*) as total FROM leave_management ${queryWhere}`
            const [count] = await db_execute(queryCount, queryParams)

            // 获取员工和批准人姓名
            for (const row of rows) {
                const employee_id = row.employee_id;
                const leave_approved_by = row.leave_approved_by;

                // 合并成一个查询，获取员工姓名
                const [result] = await db_execute(`select name from employees where employee_id = ?`, [employee_id]);

                // 处理查询结果
                if (result.length > 0) {
                    row.name = result[0].name;  // 员工姓名
                }

                // 如果leave_approved_by不为null，才查询批准人姓名
                if (leave_approved_by !== null) {
                    const [result2] = await db_execute(`select name from employees where employee_id = ?`, [leave_approved_by]);

                    // 处理批准人查询结果
                    if (result2.length > 0) {
                        row.leave_approved_name = result2[0].name;  // 批准人姓名
                    }
                }
            }



            return {
                data: rows,
                total: count[0].total
            }

        } catch (error) {
            throw new Error('获取请假记录失败: ' + error.message)

        }
    }

    static async updateLeave(leave_approved_by, type, id) {

        try {
            const query = `UPDATE leave_management SET leave_status = ? , leave_approved_by = ? WHERE leave_id = ?`
            const [result] = await db_execute(query, [type, leave_approved_by, id])
            return result.affectedRows
        } catch (error) {
            throw new Error('更新请假记录失败: ' + error.message)

        }
    }

    static async deleteLeave(id) {

        try {
            const query = `DELETE FROM leave_management WHERE leave_id = ?`
            const [result] = await db_execute(query, [id])
            return result.affectedRows
        } catch (error) {
            throw new Error('删除请假记录失败: ' + error.message)

        }
    }
}

module.exports = Leave